package com.mes.handler;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.codehaus.jettison.json.JSONObject;
import org.hibernate.Query;
import org.openbravo.dal.service.OBDal;
import org.openbravo.dal.service.OBQuery;
import org.openbravo.model.ad.access.User;
import org.openbravo.utils.FormatUtilities;
import com.basis.utils.BaseServletHandler;
import com.mes.data.EmesGroup;
import com.mes.data.EmesGroupStaff;
import com.mes.data.EmesSkill;
import com.mes.utils.ReturnSheetUtils;

public class UserImporterHandler extends BaseServletHandler {
	
	public static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	
	public static final String sexStr = "男女";
	
	// 工种技能Map判断是否重复
	private  Map<String,EmesSkill> skillMap = new HashMap<String, EmesSkill>();
	// 用户Map获取或新增用户
	private  Map<String,User> userMap = new HashMap<String, User>();
	// 班组是否重复
	private Map<String,EmesGroup> groupMap = new HashMap<String,EmesGroup>();

	@Override
	protected JSONObject service(HttpServletRequest request, HttpServletResponse response) {
		JSONObject json = new JSONObject();
		try {
			initRouteMap();
			String p_id = request.getParameter("Data_ID");
			JSONObject result = ReturnSheetUtils.getSheet(request, response);
			json = insertUserAndSkill((Sheet) result.get("sheet"),p_id);
			if(json.getBoolean("flag")){
				OBDal.getInstance().commitAndClose();
			}else{
				OBDal.getInstance().rollbackAndClose();
			}
		} catch (Exception e) {
			OBDal.getInstance().rollbackAndClose();
			try {
				json.put("flag", false);
				json.put("message", "导入失败");
			} catch (Exception ex) {
			}
			e.printStackTrace();
		}
		return json;
	}

	
	/**
	 * 插入工艺路线
	 * @param sheet
	 * @return
	 * @throws Exception
	 */

	// CELL_TYPE_NUMERIC 数值型 0
	// CELL_TYPE_STRING 字符串型 1
	// CELL_TYPE_FORMULA 公式型 2
	// CELL_TYPE_BLANK 空值 3
	// CELL_TYPE_BOOLEAN 布尔型 4
	// CELL_TYPE_ERROR 错误 5
	private String readValue(Cell cell) throws Exception {
		if (cell == null) {
			return "";
		}
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			Double text = cell.getNumericCellValue();
			return String.valueOf(text.longValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			return cell.getStringCellValue();
		} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
			throw new Exception("第" + (cell.getRowIndex() + 1) + "行，" + (cell.getColumnIndex() + 1) + "列，数据格式有问题,请检查");
		} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else {
			return "";
		}
	}

	
	
	/**
	 * 插入工种技能
	 * @return
	 * @throws Exception 
	 */
	private JSONObject insertUserAndSkill(Sheet sheet,String p_id) throws Exception{
		JSONObject result = new JSONObject();
		//最后一行
		int lastRow = sheet.getLastRowNum();
		
		String groupName = null;
		
		if(p_id.equalsIgnoreCase("")){
			 groupName = sheet.getSheetName();
		}else{
			EmesGroup eg = (EmesGroup)OBDal.getInstance().get(EmesGroup.class, p_id);
			groupName = eg.getName();
		}
		
		for(int i = 2;i<lastRow;i++){
			Row row = sheet.getRow(i);
			//用户名
			String userName = readValue(row.getCell(1));
			//用户编码
			String userCode = readValue(row.getCell(7));
			//用户性别
			String sex = readValue(row.getCell(3));
			//用户职务
			String work = readValue(row.getCell(4));
			//入单位时间
			String inTime = readValue(row.getCell(5));
			//技能等级
			String skillLvl = readValue(row.getCell(6));
			//用户登录密码(用户编码)
			String passwd = readValue(row.getCell(2));
			
			//判断数据错误
			if(userName.equalsIgnoreCase("")||userCode.equalsIgnoreCase("")||work.equalsIgnoreCase("")||passwd.equalsIgnoreCase("")){
				result.put("flag", false);
				result.put("message", "【第"+i+"行数据格式有问题，请仔细检查】");
				continue;
			}
			
			if(!sex.equalsIgnoreCase("")){
				if(!sexStr.contains(sex)){
					result.put("flag", false);
					result.put("message", "【第"+i+"行,性别字段填写不正确，请仔细检查");
					continue;
				}
			}
			
			try{
				sdf.parse(inTime);
			}catch(Exception e){
				result.put("flag", false);
				result.put("message", "【第"+i+"行入职时间填写不正确，请仔细检查");
				continue;
			}
			
			User u = null;
			if(!userMap.containsKey(userCode)){
				u = insertUsert(userName,userCode,sex,passwd,inTime,skillLvl,work);
				userMap.put(userCode, u);
			}else{
				u = userMap.get(userCode);
			}
			
			EmesGroup egs = null;
			if(!groupMap.containsKey(groupName)){
				egs = insertEmesGroup(groupName);
				groupMap.put(groupName, egs);
			}else{
				egs = groupMap.get(groupName);
			}
			
			//班组员工
			Long GstaffCount = getEmesGroupStaff(u.getId(),egs.getId());
			//工种技能员工
//			Long SstaffCount = getEmesSkillStaff(u.getId(),es.getId());
			
			if(GstaffCount == 0 ){
				EmesGroupStaff egss = new EmesGroupStaff();
				egss.setUser(u);
				egss.setEmesGroup(egs);
				OBDal.getInstance().save(egss);
			}
			
			result.put("flag", true);
			result.put("message", "导入成功");
		}
		return result;
	}

	
	/**
	 * 初始化routeMap
	 * @param  
	 */
	private void initRouteMap(){
		String sql2 = "select * from ad_user";
		Query query2 = OBDal.getInstance().getSession().createSQLQuery(sql2).addEntity(User.ENTITY_NAME);
		List<User> userList = query2.list();
		for(User o:userList){
			userMap.put(o.getUsername(), o);
		}
		String sql3 = "select * from emes_group";
		Query query3 = OBDal.getInstance().getSession().createSQLQuery(sql3).addEntity(EmesGroup.ENTITY_NAME);
		List<EmesGroup> groupList = query3.list();
		for(EmesGroup o:groupList){
			groupMap.put(o.getName(), o);
		}
	}
	
	/**
	 * 获取用户
	 * @param userName
	 * @return
	 */
	private User getUser(String userName){
		OBQuery<User> createQuery = OBDal.getInstance().createQuery(User.class, "username=:userName");
		createQuery.setNamedParameter("userName", userName);
		User user = createQuery.uniqueResult();
		return user;
	}
	
	
	/**
	 * 插入用户信息
	 * @param userName
	 * @param passwd
	 * @param sex
	 * @throws ServletException 
	 * @throws ParseException 
	 */
	private User insertUsert(String userName,String userCode,String sex,String passwd,String inTime,String skillLvl,String work) throws ServletException, ParseException{
		User u = new User();
		String password = FormatUtilities.sha1Base64(passwd);
		u.setPassword(password);
		u.setUsername(userCode);
		u.setName(userName);
		u.setOccupationalClass(work);
		if(!inTime.equalsIgnoreCase("")){
			u.setEntryTime(sdf.parse(inTime));
		}
		u.setSex(sex);
		u.setDescription(skillLvl);
//		u.set
		OBDal.getInstance().save(u);
		return u;
	}
	
	
	/**
	 * 插入班组
	 * @return
	 */
	private EmesGroup insertEmesGroup(String groupName){
		EmesGroup eg = new EmesGroup();
		eg.setName(groupName);
		OBDal.getInstance().save(eg);
		return eg;
	}
	
	
	/**
	 * 插入工种技能
	 * @param name
	 * @param desc
	 */
	private EmesSkill insertSkill(String name,String desc){
		EmesSkill es = new EmesSkill();
		es.setSkillName(name);
//		es.setSkillLevel(desc);
		es.setSkillDescription(desc);
		OBDal.getInstance().save(es);
		return es;
	}
	
	
	
	
	/**
	 * 查询同一班组下是否有重复员工
	 * @return
	 */
	private long getEmesGroupStaff(String userId,String egId){
		String sql = "select count(*) from Emes_Group_Staff egs where egs.ad_user_id = ? and egs.Emes_Group_id = ?";
		Query query = OBDal.getInstance().getSession().createSQLQuery(sql);
		query.setString(0, userId);
		query.setString(1, egId);
		return (Long)query.uniqueResult();
	}
	
	
	/**
	 * 查询同一工种技能下下是否有重复员工
	 * @return
	 */
	private long getEmesSkillStaff(String userId,String esId){
		String sql = "select count(*) from Emes_Skill_Staff ess where ess.ad_user_id = ? and ess.Emes_skill_id = ?";
		Query query = OBDal.getInstance().getSession().createSQLQuery(sql);
		query.setString(0, userId);
		query.setString(1, esId);
		return (Long)query.uniqueResult();
	}
	
	

}
